﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XLETL.Model;
using XLETL.IDAL;

namespace XLETL.SQLServerDAL
{
    public class ETL : IETL
    {
        public const string START_TRANS = "Begin Tran XLETL_TRAN_{0};"; // use target table name for uniquness
        public const string COMMIT_TRANS = "Commit Tran XLETL_TRAN_{0};";
        public const string ROLLBACK_TRANS = "Rollback Tran XLETL_TRAN_{0};";
        
        public const string CREATE_TEMP_TABLE = "Create Table #XLETL_TEMP_{0}("; // use target table name for uniquness
        public const string DROP_TEMP_TABLE = "Drop Table #XLETL_TEMP_{0};"; 
        public const string TEMP_TABLE_COLUMNS = "{0} {1}({2}), "; // similiar output: Username nvarchar(50)
        public const string APPEND_ACTION_ID = "XLETL_ActionID int );";

        public const string INSERT_BEGIN = "INSERT INTO [{0}] (";
        public const string INSERT_COLUMNS = "[{0}],";
        public const string INSERT_END = ") VALUES({0});";

        public const string UPDATE_BEGIN = "UPDATE [{0}] SET ";

        public const string WHERE_CLAUSE = " WHERE ";
        public const string WHERE_ACTIONID = " WHERE XLETL_ActionID IS NULL";
        public const string AND_CLAUSE = " AND ";
        public const string A_B_CLAUSE = "A.{0} = B.{0}";
        public const string COLUMN_A_CLAUSE = "{0} = A.{0},";
        public const string A_TABLE_CLAUSE = "A.{1} = [{0}].{1}";        

        public const string COL_ROWGUID = "rowguid";

        StringBuilder sqlQueryPattern = null;

        /// <summary>
        /// Method to build create temp table: Create Table #XLETL_TEMP_Table(Username nvarchar(50), Password nvarchar(50), ActionID int );
        /// </summary>
        /// <param name="taskInfo"></param>
        /// <returns></returns>
        public string BuildCreateTempTableStatement(TaskInfo taskInfo)
        {
            sqlQueryPattern = new StringBuilder();
            sqlQueryPattern.AppendFormat("Create Table #XLETL_TEMP_{0}(", taskInfo.TargetTableName);

            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
                if (tci.IsIdentity)
                {
                }
                else
                {
                  if (tci.Type.ToLower().Contains("int") || tci.Type.ToLower().Contains("money") || tci.Type.ToLower().Contains("text") || tci.Type.ToLower().Equals("image") || tci.Type.ToLower().Equals("bit") || tci.Type.ToLower().Equals("real") || tci.Type.ToLower().Equals("datetime"))
                    {
                        sqlQueryPattern.AppendFormat("{0} {1}", tci.Name, tci.Type);
                    }
                    else
                    {
                      if (tci.Size != -1)
                      {
                        sqlQueryPattern.AppendFormat("{0} {1}({2})", tci.Name, tci.Type, tci.Size);
                      }
                      else
                      {
                        sqlQueryPattern.AppendFormat("{0} {1}({2})", tci.Name, tci.Type, "max");
                      }
                    }                  

                  // Append ,
                  sqlQueryPattern.Append(", ");
                }
            }

            // now add the flag column for identifying existing records
            sqlQueryPattern.Append("XLETL_ActionID int );");
            sqlQueryPattern.AppendLine();

            return sqlQueryPattern.ToString();
        }
        
        /// <summary>
        /// Method to build insert statement: Insert Into [Table] ([Column1],[Column2]) Values({0});
        /// </summary>
        /// <param name="taskInfo"></param>
        /// <returns></returns>
        public string BuildInsertStatement(TaskInfo taskInfo, bool isForTemp)
        {
            sqlQueryPattern = new StringBuilder();
            string tableName = isForTemp ? "#XLETL_TEMP_" + taskInfo.TargetTableName : taskInfo.TargetTableName;

            sqlQueryPattern.AppendFormat(INSERT_BEGIN, tableName); // "INSERT INTO [{0}] ("

            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
                // if it is autoincrement then ignore it
                // TODO: it might be useful to implement SET IDENTITY_INSERT [TABLE_NAME] ON / OFF . 
                if (tci.IsIdentity || tci.Name.ToLower().Equals(COL_ROWGUID))
                {
                }
                else
                {
                    sqlQueryPattern.AppendFormat(INSERT_COLUMNS, tci.Name); // "[{0}],"
                }
            }
            sqlQueryPattern.Remove(sqlQueryPattern.Length - 1, 1); // remove the last ,
            sqlQueryPattern.Append(INSERT_END); // ") VALUES({0});"

            return sqlQueryPattern.ToString();
        }

        // Update ActionID to mark non-existing records
        public string BuildUpdateTempTableStatement(TaskInfo taskInfo)
        {
            sqlQueryPattern = new StringBuilder();
            StringBuilder sqlWhereClause = new StringBuilder(WHERE_CLAUSE); // " WHERE "

            sqlQueryPattern.AppendFormat("UPDATE #XLETL_TEMP_{0} SET XLETL_ActionID=1 FROM #XLETL_TEMP_{0} AS A LEFT JOIN {0} B ON ", taskInfo.TargetTableName);

            int counter = 0;
            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
              // Check if mapping exist and the mapped source column is the key field
              SourceColumnInfo sci = taskInfo.SourceColumns.Find(delegate(SourceColumnInfo search)
              {
                if (search.TargetColumnID.Equals(tci.ID))
                  return true;
                else
                  return false;
              });

              if (sci != null && sci.IsKeyField)
                //if (tci.IsKeyField)
                {
                    if (counter > 0)
                    {
                        sqlQueryPattern.Append(AND_CLAUSE); // " AND "
                        sqlWhereClause.Append(AND_CLAUSE); // " AND "
                    }
                    sqlQueryPattern.AppendFormat(A_B_CLAUSE, tci.Name); // "A.{0} = B.{0}"

                    // Append collation for column types: char, varchar, text, nchar, nvarchar, ntext
                    if (tci.Type.ToLower().Contains("char") || tci.Type.ToLower().Contains("text"))
                    {
                      sqlQueryPattern.Append(" COLLATE database_default");
                    }

                    sqlWhereClause.AppendFormat("B.{0} IS NULL", tci.Name);
                    counter++;
                }
                
            }
            sqlQueryPattern.Append(sqlWhereClause);

            return sqlQueryPattern.ToString();
        }

        // Insert New: already existing records are skipped, and new records are inserted into the destination table.
        public string BuildInsertNewStatement(TaskInfo taskInfo)
        {
            sqlQueryPattern = new StringBuilder();
            StringBuilder sqlSelectClause = new StringBuilder("Select ");

            sqlQueryPattern.AppendFormat(INSERT_BEGIN, taskInfo.TargetTableName); // "INSERT INTO [{0}] ("

            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
                if (tci.IsIdentity || tci.Name.ToLower().Equals(COL_ROWGUID))
                {
                }
                else
                {
                    sqlQueryPattern.AppendFormat(INSERT_COLUMNS, tci.Name); // "[{0}],"
                    sqlSelectClause.AppendFormat("{0},", tci.Name);
                }
            }
            sqlQueryPattern.Remove(sqlQueryPattern.Length - 1, 1); // remove the last ,
            sqlSelectClause.Remove(sqlSelectClause.Length - 1, 1); // remove the last ,

            sqlQueryPattern.Append(") ");
            sqlQueryPattern.Append(sqlSelectClause.ToString());
            sqlQueryPattern.AppendFormat(" FROM #XLETL_TEMP_{0} WHERE XLETL_ActionID = 1", taskInfo.TargetTableName);

            return sqlQueryPattern.ToString();            
        }

        // Update Records which are not marked with ActionID
        public string BuildUpdateExistingStatement(TaskInfo taskInfo)
        {
            sqlQueryPattern = new StringBuilder();
            StringBuilder sqlJoinClause = new StringBuilder();            

            sqlQueryPattern.AppendFormat(UPDATE_BEGIN, taskInfo.TargetTableName); // "UPDATE [{0}] SET "

            int counter = 0;
            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
              // Check if mapping exist and the mapped source column is the key field
              SourceColumnInfo sci = taskInfo.SourceColumns.Find(delegate(SourceColumnInfo search)
              {
                if (search.TargetColumnID.Equals(tci.ID))
                  return true;
                else
                  return false;
              });

              if (sci != null && sci.IsKeyField)
                //if (tci.IsKeyField)
                {
                    if (counter > 0)
                    {
                        sqlJoinClause.Append(AND_CLAUSE); // " AND "                       
                    }

                    sqlJoinClause.AppendFormat(A_B_CLAUSE, tci.Name); // "A.{0} = B.{0}"

                    // Append collation for column types: char, varchar, text, nchar, nvarchar, ntext
                    if (tci.Type.ToLower().Contains("char") || tci.Type.ToLower().Contains("text"))
                    {
                      sqlJoinClause.Append(" COLLATE database_default");
                    }

                    sqlQueryPattern.AppendFormat(COLUMN_A_CLAUSE, tci.Name); // "{0} = A.{0},"
                    counter++;
                }
                else if (!tci.IsIdentity && !tci.Name.ToLower().Equals(COL_ROWGUID))
                {
                    sqlQueryPattern.AppendFormat("{0} = A.{0},", tci.Name);                    
                }              
              
            }

            sqlQueryPattern.Remove(sqlQueryPattern.Length - 1, 1); // remove the last ,             
            sqlQueryPattern.AppendFormat(" FROM [{0}] AS B JOIN #XLETL_TEMP_{0} AS A ON ", taskInfo.TargetTableName);
                                  
            sqlQueryPattern.Append(sqlJoinClause.ToString());
            sqlQueryPattern.AppendLine(WHERE_ACTIONID); // " WHERE XLETL_ActionID IS NULL"

            return sqlQueryPattern.ToString();            
        }

        // Delete Records which are not marked with ActionID
        public string BuildDeleteExistingStatement(TaskInfo taskInfo)
        {
            sqlQueryPattern = new StringBuilder();

            sqlQueryPattern.AppendFormat("DELETE FROM [{0}] WHERE EXISTS (SELECT * FROM #XLETL_TEMP_{0} AS A WHERE ", taskInfo.TargetTableName);
            
            int counter = 0;
            foreach (TargetColumnInfo tci in taskInfo.TargetColumns)
            {
              // Check if mapping exist and the mapped source column is the key field
              SourceColumnInfo sci = taskInfo.SourceColumns.Find(delegate(SourceColumnInfo search)
              {
                if (search.TargetColumnID.Equals(tci.ID))
                  return true;
                else
                  return false;
              });

              if (sci != null && sci.IsKeyField)
                //if (tci.IsKeyField)
                {
                    if (counter > 0)
                    {
                        sqlQueryPattern.Append(AND_CLAUSE); // " AND "
                    }

                    sqlQueryPattern.AppendFormat(A_TABLE_CLAUSE, taskInfo.TargetTableName, tci.Name); // "A.{1} = [{0}].{1}"
                    counter++;
                }                
            }

            sqlQueryPattern.Append(" AND A.XLETL_ActionID IS NULL);");            

            return sqlQueryPattern.ToString();           
        }

        public bool ExecuteSqlScript(string sqlQuery, string connString, bool testRun)
        {
            SqlTransaction sqlTran = null;
            SqlConnection con = null;
            bool isSuccess = true;
            
            using (con = new SqlConnection(connString))
            {
                try
                {
                    // 1. Open a database connection
                    con.Open();
                    // 2. Begin a transaction
                    sqlTran = con.BeginTransaction();

                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.Transaction = sqlTran;
                        com.CommandType = CommandType.Text;
                        com.CommandText = sqlQuery;
                        
                        // 3. Fire query against the connection via the command object
                        com.ExecuteNonQuery();
                        
                        // 4. Commit or Rollback the transaction
                        if (testRun)
                        {
                          sqlTran.Rollback();
                        }
                        else
                        {
                          sqlTran.Commit();
                        }
                    }
                }
                catch (Exception ex)
                {
                    sqlTran.Rollback();
                    isSuccess = false;
                    throw ex;
                }
                finally
                {
                    // 5. Close the connection
                    con.Close();
                }

            }

            return isSuccess;
        }

        /// <summary>
        /// Begin Tran XLETL_TRAN_{0}
        /// </summary>
        public string GetStartTranString
        {
            get { return START_TRANS; }
        }
        /// <summary>
        /// Commit Tran XLETL_TRAN_{0}
        /// </summary>
        public string GetCommitTranString
        {
            get { return COMMIT_TRANS; }
        }
        /// <summary>
        /// Rollback Tran XLETL_TRAN_{0}
        /// </summary>
        public string GetRollbackTranString
        {
            get { return ROLLBACK_TRANS; }
        }
        /// <summary>
        /// Drop Table #XLETL_TEMP_{0}
        /// </summary>
        public string GetDropTableString
        {
            get { return DROP_TEMP_TABLE; }
        }
        
    }
}

/*
Begin Tran T1;
-- 1. Data for import inserted into temp table
Create Table #TEMPDATA(Username nvarchar(50), Password nvarchar(50), FirstName nvarchar(50), LastName nvarchar(50), Email nvarchar(50), Phone nvarchar(50), CompanyID int, ActionID int );


INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser1', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser2', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser3', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser4', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser5', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser6', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);



INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser1', 'Password', 'Firstname1', 'Lastnsame1', 'Email1', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser2', 'Password', 'Firstname2', 'Lastnsame2', 'Email2', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser3', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser7', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser8', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser9', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);



INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser1', 'Password', 'Firstname1', 'Lastnsame1', 'Email1', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser2', 'Password', 'Firstname2', 'Lastnsame2', 'Email2', '23423423', 1);
INSERT INTO #TEMPDATA (Username, Password, FirstName, LastName, Email, Phone, CompanyID) 
values ('TestUser10', 'Password', 'Firstname', 'Lastnsame', 'Email', '23423423', 1);


-- 2. Update ActionID to mark non-existing records
UPDATE #TEMPDATA SET ActionID=1
FROM #TEMPDATA as A
LEFT JOIN UserDetails B ON A.Username = B.Username and A.Password = B.Password
WHERE B.Username IS NULL AND B.Password is null


-- 3. Insert New (already existing records are skipped, and new records are inserted into the destination table)
INSERT INTO UserDetails (Username, Password, FirstName, LastName, Email, Phone, CompanyID)
Select A.Username, A.Password, A.FirstName, A.LastName, A.Email, A.Phone, A.CompanyID
FROM #TEMPDATA as A
--LEFT JOIN UserDetails B ON A.Username = B.Username and A.Password = B.Password
--WHERE B.Username IS NULL AND B.Password is null
WHERE A.ActionID = 1

-- 4. Update Records
UPDATE UserDetails SET Username=A.Username, Password=A.Password, FirstName=A.FirstName, LastName=A.LastName, Email=A.Email, Phone=A.Phone, CompanyID=A.CompanyID
from UserDetails B
join #TEMPDATA as A
on A.Username = B.Username AND A.Password = B.Password
where A.ActionID is null

-- 5. Delete Existing Records 
delete from UserDetails
where exists
(select * from #TEMPDATA A
--where A.Username = UserDetails.Username AND A.Password = UserDetails.Password
where A.ActionID is null
)


Commit Tran T1;

drop table #TEMPDATA
 */

/*

-- INSERT new records 

insert into dbo.tableB (col1, col2, ...)
select col1, col2, ...
from dbo.tableA a
where not exists
(select * from dbo.tableB b
where a.PrimaryKeyCol = b.PrimaryKeyCol)
and a.[Action] = 'INSERT'

-- UPDATE existing records

update dbo.tableB
set col1 = a.col1, col2 = a.col2, ...
from dbo.tableB b
join dbo.tableA a
on a.PrimaryKeyCol = b.PrimaryKeyCol
where a.[Action] = 'UPDATE'

-- DELETE existing records 

delete from dbo.tableB
where exists
(select * from dbo.tableA a
where a.PrimaryKeyCol = dbo.tableB.PrimaryKeyCol
and a.[Action] = 'DELETE')
*/
